﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Configuration;
using Options.IBClient.Component.Enums;

namespace Options.IBClient.Component
{
    public class SqlHelper
    {
        #region Security
        public List<Security> GetSymbols()
        {
            List<Security> securities = new List<Security>();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_getSymbols", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Security security = new Security();
                                security.SecurityId = ReadDataReaderColumn<int>(reader, "Id");
                                security.Symbol = ReadDataReaderColumn<string>(reader, "Symbol").Trim();
                                security.Definition = ReadDataReaderColumn<string>(reader, "Definition");
                                security.IsWeekly = ReadDataReaderColumn<bool>(reader, "IsWeeklyOption");
                                securities.Add(security);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return securities;
        }



        public List<Security> UpdateIsWeeklySymbols(string Symbol, bool IsWeeklyOption)
        {
            List<Security> securities = new List<Security>();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateIsWeeklySecurities", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Symbol", Symbol);
                        command.Parameters.AddWithValue("@IsWeeklyOption", IsWeeklyOption);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return securities;
        }

        public List<Security> GetSymbolslForOptionUpdateRequests()
        {
            List<Security> securities = new List<Security>();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_getSymbolForOptionUpdate", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Security security = new Security();
                                security.SecurityId = ReadDataReaderColumn<int>(reader, "Id");
                                security.Symbol = ReadDataReaderColumn<string>(reader, "Symbol").Trim();
                                security.Definition = ReadDataReaderColumn<string>(reader, "Definition");
                                security.IsWeekly = ReadDataReaderColumn<bool>(reader, "IsWeeklyOption");
                                securities.Add(security);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return securities;
        }


        public List<RequestIBClient> GetSymbolslForInventoryRequests()
        {
            List<RequestIBClient> rIBs = new List<RequestIBClient>();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_getOptionInventory", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                RequestIBClient rIB = new RequestIBClient();
                                rIB.SecurityId = ReadDataReaderColumn<int>(reader, "Id");
                                rIB.SecurityType = Options.IBClient.Component.Enums.SecurityType.Option;
                                rIB.RightType = (String.Equals(ReadDataReaderColumn<string>(reader, "Right_Type").Trim(),"PUT",StringComparison.OrdinalIgnoreCase)) ? RightType.Put : RightType.Call;
                                rIB.Expiry = ReadDataReaderColumn<DateTime>(reader, "Expiry");
                                rIB.StrikePrice = ReadDataReaderColumn<decimal>(reader, "Strike_Price");
                                rIBs.Add(rIB);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return rIBs;
        }

        public List<Security> GetSymbolslForMarketDataRequests()
        {
            List<Security> securities = new List<Security>();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_getSymbolForMarketData", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Security security = new Security();
                                security.SecurityId = ReadDataReaderColumn<int>(reader, "Id");
                                security.Symbol = ReadDataReaderColumn<string>(reader, "Symbol").Trim();
                                security.Definition = ReadDataReaderColumn<string>(reader, "Definition");
                                securities.Add(security);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return securities;
        }

        public void AddSymbol(string Symbol, string Definition)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_AddModifySecurity", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Symbol", Symbol);
                        command.Parameters.AddWithValue("@Definition", Definition);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }

        }


        public string GetSymbolbyRequest(int id)
        {
            string symbol = string.Empty;
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_getSymbolbyRequestId", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Id", id);
                        object result = command.ExecuteScalar();
                        symbol = result == null ? string.Empty : (string)result;

                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return symbol;
        }

        public void DeleteSymbol(string Symbol)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_DeleteSecurity", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Symbol", Symbol);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }
        #endregion

        #region Settings
        public List<Settings> GetSettings()
        {
            List<Settings> settings = new List<Settings>();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_getSettings", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Settings setting = new Settings();
                                setting.Name = ReadDataReaderColumn<string>(reader, "Name");
                                setting.Min = ReadDataReaderColumn<decimal>(reader, "Min");
                                setting.Max = ReadDataReaderColumn<decimal>(reader, "Max");
                                setting.Type = ReadDataReaderColumn<string>(reader, "Type");
                                settings.Add(setting);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return settings;
        }

        public void UpdateSettings(Settings settings)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateSettings", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Name", settings.Name);
                        command.Parameters.AddWithValue("@Min", settings.Min);
                        command.Parameters.AddWithValue("@Max", settings.Max);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }
        #endregion

        #region Common Settings
        public List<CommonSettings> GetCommonSettings()
        {
            List<CommonSettings> settings = new List<CommonSettings>();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetCommonSettings", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                CommonSettings setting = new CommonSettings();
                                setting.Name = ReadDataReaderColumn<string>(reader, "Name");
                                setting.Value = ReadDataReaderColumn<string>(reader, "Value");
                                settings.Add(setting);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return settings;
        }

        public CommonSettings GetCommonSettingbyName(string Name)
        {
            CommonSettings setting = new CommonSettings();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetCommonSettingByName", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Name", Name);
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                setting.Name = ReadDataReaderColumn<string>(reader, "Name");
                                setting.Value = ReadDataReaderColumn<string>(reader, "Value");
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return setting;
        }

        public void UpdateCommonSettings(CommonSettings settings)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateCommonSettingByName", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Name", settings.Name);
                        command.Parameters.AddWithValue("@Value", settings.Value);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }
        #endregion

        #region Market Data
        public void AddMarketData(MarketData marketData)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_AddMarketData", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;

                        command.Parameters.AddWithValue("@Symbol", marketData.Symbol);
                        command.Parameters.AddWithValue("@Tick_Type", marketData.TickType);
                        command.Parameters.AddWithValue("@Tick_Price", marketData.TickPrice);
                        command.ExecuteNonQuery();

                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

        public List<MarketData> DisplayMarketData()
        {
            List<MarketData> marketdata = new List<MarketData>();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_getMarketData", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                MarketData mD = new MarketData();
                                mD.Symbol = ReadDataReaderColumn<string>(reader, "Symbol");
                                mD.DateAdded = ReadDataReaderColumn<DateTime>(reader, "DateAdded");
                                mD.TickType = ReadDataReaderColumn<string>(reader, "Tick_Type");
                                mD.TickPrice = ReadDataReaderColumn<decimal>(reader, "Tick_Price");
                                mD.MinStrikePrice = ReadDataReaderColumn<int>(reader, "MinStrikePrice");
                                mD.MaxStrikePrice = ReadDataReaderColumn<int>(reader, "MaxStrikePrice");
                                marketdata.Add(mD);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return marketdata;
        }

        public MarketData GetMarketDataBySymbol(string symbol)
        {
            MarketData mD = new MarketData();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_getMarketDataBySymbol", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Symbol", symbol);
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {

                                mD.Symbol = ReadDataReaderColumn<string>(reader, "Symbol");
                                mD.DateAdded = ReadDataReaderColumn<DateTime>(reader, "DateAdded");
                                mD.TickType = ReadDataReaderColumn<string>(reader, "Tick_Type");
                                mD.TickPrice = ReadDataReaderColumn<decimal>(reader, "Tick_Price");
                                mD.MinStrikePrice = ReadDataReaderColumn<int>(reader, "MinStrikePrice");
                                mD.MaxStrikePrice = ReadDataReaderColumn<int>(reader, "MaxStrikePrice");
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return mD;
        }

        public void PurgeStockData()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.PurgeStockData", connection))
                    {
                        string requestId = string.Empty;
                        command.CommandType = CommandType.StoredProcedure;
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }
        #endregion

        #region Delta Data
        public List<OptionComputation> GetLatestOptionDataBySymbolRightType(string symbol, string rightType)
        {
            List<OptionComputation> optionComputations = new List<OptionComputation>();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_getLatestOptionDataBySymbolRightType", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Right_Type", rightType);
                        command.Parameters.AddWithValue("@Symbol", symbol);

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                OptionComputation optionComputation = new OptionComputation();
                                optionComputation.Symbol = symbol;
                                optionComputation.RightType = rightType == RightType.Put.ToString() ? RightType.Put : RightType.Call;
                                optionComputation.Expiry = ReadDataReaderColumn<DateTime>(reader, "Expiry");
                                optionComputation.StrikePrice = ReadDataReaderColumn<decimal>(reader, "Strike_Price");
                                optionComputation.AskPrice = ReadDataReaderColumn<decimal>(reader, "Ask_Price");
                                optionComputation.BidPrice = ReadDataReaderColumn<decimal>(reader, "Bid_Price");
                                optionComputation.Delta = ReadDataReaderColumn<double>(reader, "Delta");
                                optionComputation.Theta = ReadDataReaderColumn<double>(reader, "Theta");
                                optionComputation.Gamma = ReadDataReaderColumn<double>(reader, "Gamma");
                                optionComputation.Vega = ReadDataReaderColumn<double>(reader, "Vega");
                                optionComputations.Add(optionComputation);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return optionComputations;
        }

        public List<OptionTrigger> GetOptionTrigger()
        {
            List<OptionTrigger> optionTriggers = new List<OptionTrigger>();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_getOptionTrigger", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                OptionTrigger optionTrigger = new OptionTrigger();
                                optionTrigger.Id = ReadDataReaderColumn<int>(reader, "Id");
                                optionTrigger.Symbol = ReadDataReaderColumn<string>(reader, "Symbol");
                                optionTrigger.RightType = ReadDataReaderColumn<string>(reader, "Right_Type").Trim() == RightType.Put.ToString() ? RightType.Put : RightType.Call; ;
                                optionTrigger.Expiry = ReadDataReaderColumn<DateTime>(reader, "Expiry");
                                optionTrigger.RefreshDate = ReadDataReaderColumn<DateTime>(reader, "RefreshDate");
                                optionTrigger.StrikePrice = ReadDataReaderColumn<decimal>(reader, "Strike_Price");
                                optionTrigger.AskPrice = ReadDataReaderColumn<decimal>(reader, "Ask_Price");
                                optionTrigger.BidPrice = ReadDataReaderColumn<decimal>(reader, "Bid_Price");
                                optionTrigger.Delta = ReadDataReaderColumn<double>(reader, "Delta");
                                optionTrigger.Theta = ReadDataReaderColumn<double>(reader, "Theta");
                                optionTrigger.Gamma = ReadDataReaderColumn<double>(reader, "Gamma");
                                optionTrigger.Vega = ReadDataReaderColumn<double>(reader, "Vega");
                                optionTrigger.PlaceOrder = ReadDataReaderColumn<bool>(reader, "PlaceOrder");
                                optionTriggers.Add(optionTrigger);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return optionTriggers;
        }
        #endregion

        #region Request and Response
        public RequestIBClient AddRequest(RequestIBClient request)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_AddRequestIBClient", connection))
                    {

                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Security_Id", request.SecurityId);
                        command.Parameters.AddWithValue("@Security_Type", request.SecurityType.ToString());
                        command.Parameters.AddWithValue("@Expiry", request.Expiry);
                        command.Parameters.AddWithValue("@Strike_Price", request.StrikePrice);
                        command.Parameters.AddWithValue("@Right_Type", request.RightType.ToString());
                        command.Parameters.AddWithValue("@QueueIdentifier", request.QueueId);
                        command.Parameters.AddWithValue("@IsWeeklyOption", request.IsWeeklyOption);
                        SqlParameter parameter = command.Parameters.Add("@RequestId", SqlDbType.Int);
                        parameter.Direction = ParameterDirection.Output;
                        command.ExecuteNonQuery();

                        request.RequestId = Convert.ToInt32(parameter.Value);
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return request;
        }


        public int GetCountPendingReqSubmitted()
        {
            int pendingCount = 0;
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_CountPendingReqSubmitted", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        object result = command.ExecuteScalar();
                        pendingCount = result == null ? 0 : (int)result;

                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return pendingCount;
        }


        public DataSet GetTrackRequests()
        {
            DataSet ds = new DataSet(); ;
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.TrackRequests", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        SqlDataAdapter da = new SqlDataAdapter();
                        da.SelectCommand = command;
                        da.Fill(ds);
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return ds;
        }



        public void UpdatePurgeRequests()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdatePurgeRequests", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

        public List<RequestIBClient> GetPendingRequest()
        {
            List<RequestIBClient> requests = new List<RequestIBClient>();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetPendingRequest", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                RequestIBClient request = new RequestIBClient();
                                request.RequestId = int.Parse(reader.GetValue(reader.GetOrdinal("RequestId")).ToString());
                                request.SecurityId = int.Parse(reader.GetValue(reader.GetOrdinal("Security_Id")).ToString());
                                request.SecurityType = reader.GetValue(reader.GetOrdinal("Security_Type")).ToString().Trim() == SecurityType.Stock.ToString() ? SecurityType.Stock : SecurityType.Option;
                                request.Expiry = DateTime.Parse(reader.GetValue(reader.GetOrdinal("Expiry")).ToString());
                                request.StrikePrice = decimal.Parse(reader.GetValue(reader.GetOrdinal("Strike_Price")).ToString());
                                request.RightType = reader.GetValue(reader.GetOrdinal("Right_Type")).ToString().Trim() == RightType.Put.ToString() ? RightType.Put : RightType.Call;
                                requests.Add(request);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return requests;
        }

        public void AddResponse(ResponseIBClient response)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_AddResponseIBClient", connection))
                    {
                        string requestId = string.Empty;
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@RequestId", response.RequestId);
                        command.Parameters.AddWithValue("@Tick_Type", response.TickType);
                        command.Parameters.AddWithValue("@Tick_Price", response.TickPrice);
                        command.Parameters.AddWithValue("@Delta", response.Delta);
                        command.Parameters.AddWithValue("@Gamma", response.Gamma);
                        command.Parameters.AddWithValue("@Theta", response.Theta);
                        command.Parameters.AddWithValue("@Vega", response.Vega);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

        public void PurgeRequestResponseData()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.PurgeRequestResponseData", connection))
                    {
                        string requestId = string.Empty;
                        command.CommandType = CommandType.StoredProcedure;
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

        public void UpdateRequestReceived(int ReqId)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateRequestIBClientReceived", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@RequestId", ReqId);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }


        public bool UpdateRequestIBClientError(int ReqId)
        {
            bool ret = false;
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateRequestIBClientError", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@RequestId", ReqId);
                        object result = command.ExecuteScalar();
                        ret = result == null ? false : (bool)result;
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return ret;
        }
        public void UpdateRequestIBClientSubmitted(int ReqId)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateRequestIBClientSubmitted", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@RequestId", ReqId);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }



        #endregion

        #region Utils

        public DataTable getExpiryDates()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_getOptionExpiry", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return dt;
        }


        public DataTable getWeeklyExpiryDates()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_getWeeklyOptionExpiry", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return dt;
        }


        public DataTable getCompleteWeeklyExpiryDates()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_getCompleteWeeklyOptionExpiry", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return dt;
        }


        public void UpdateOptionExpiry(DateTime dtexpiry, bool Check)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateOptionExpiry", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@ExpiryDate", dtexpiry);
                        command.Parameters.AddWithValue("@Checked", Check);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }


        public void UpdateWeeklyOptionExpiry(DateTime dtexpiry, DateTime date)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_WeeklyOptionExpiry", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Date", date);
                        command.Parameters.AddWithValue("@Expiry", dtexpiry);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

        private static T ReadDataReaderColumn<T>(IDataReader dr, string columnName)
        {
            int ordinal = dr.GetOrdinal(columnName);

            if (dr.IsDBNull(ordinal))
                return default(T);
            else
                return (T)dr.GetValue(ordinal);
        }
        #endregion Utils

        #region Make Data
        public void MakeMarketData()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.MakeMarketData", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

        public void MakeOptiontData()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.MakeOptionData", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }
        #endregion

        #region Order

        public void AddRequestOrderData(int OptionTriggerId, int OrderId, string action, int size, double price)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_AddPlaceOrderRequest", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@OptionTriggerId", OptionTriggerId);
                        command.Parameters.AddWithValue("@OrderId", OrderId);
                        command.Parameters.AddWithValue("@Action", action);
                        command.Parameters.AddWithValue("@Size", size);
                        command.Parameters.AddWithValue("@Price", price);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

        public void AddResponseData(int OrderId, double price, string status)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_AddPlaceOrderRespose", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@OrderId", OrderId);
                        command.Parameters.AddWithValue("@Price", price);
                        command.Parameters.AddWithValue("@Status", status);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

        public void UpdatePlaceOrderRequestSubmitted(int orderId)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdatePlaceOrderRequestSubmitted", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@OrderId", orderId);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

        public void UpdatePlaceOrderOptionTrigger(int optionTriggerId, bool value)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateOptionTriggerPlaceOrder", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@OptionTriggerId", optionTriggerId);
                        command.Parameters.AddWithValue("@PlaceOrder", value);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

        public DataTable GetPlaceOrderRequest()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetPlaceOrderRequest", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return dt;
        }

        public int GetNextOrderId()
        {
            int orderId = 0;
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_getNextOrderId", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        object result = command.ExecuteScalar();
                        orderId = result == null ? 0 : (int)result;
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return orderId;
        }

        public DataTable GetOpenOrders()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_getOpenComboOrders", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return dt;
        }

        public void AddOpenOrder(int openOrderId)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_AddOpenOrder", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@OpenOrderId", openOrderId);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

        public void AddCloseOrder(int openOrderId, int closeOrderId)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_AddCloseOrder", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@OpenOrderId", openOrderId);
                        command.Parameters.AddWithValue("@CloseOrderId", closeOrderId);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

        public void makeInventoryList()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.MakeInventoryList", connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }


        public void makeWeeklyInventoryList()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.MakeWeeklyInventoryList", connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }
        #endregion

        #region Log
        public void AddLog(string Message)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_AddLog", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Message", Message);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }
        #endregion

        #region Combo Leg

        public DataTable GetPendingRequestsForConId()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GETComboLegOrderRequest", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return dt;
        }

        public DataTable GetPendingRequestsForCloseConId()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetCloseComboLegOrderRequest", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return dt;
        }

        public void UpdateConId(int requestId, int conId)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateConId", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Id", requestId);
                        command.Parameters.AddWithValue("@ConId", conId);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

        public void UpdateConIdSubmitted(int requestId)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateConIdSubmitted", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Id", requestId);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

         public void UpdateCloseConIdSubmitted(int requestId)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateCloseComboConIdSubmitted", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Id", requestId);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

        public DataTable GetPlaceComboOrderRequest()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetPlaceComboOrderRequest", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
            return dt;
        }


        public void UpdateComboOrderRequestSubmitted(int orderId)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateComboOrderRequestSubmitted", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Id", orderId);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

        public void AddPlaceComboOrderRespose(int OrderId, double price, string status)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_AddPlaceComboOrderRespose", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@OrderId", OrderId);
                        command.Parameters.AddWithValue("@Price", price);
                        command.Parameters.AddWithValue("@Status", status);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }
        #endregion

        #region Connectivity
        public bool IsServerConnected()
        {
            SqlConnection connection = new SqlConnection(Common.ConnectionString);
            try
            {
                connection.Open();
                return true;
            }
            catch (Exception ex)
            {
                return false;

            }
            finally
            {
                connection.Close();
            }

        }
        #endregion

        #region SMA(Slow Moving Average)
        public void AddSMA(string symbol, string price, string sma_50, string sma_200)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_AddSMA", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Symbol", symbol);
                        command.Parameters.AddWithValue("@Stock_Price", price);
                        command.Parameters.AddWithValue("@SMA_50", sma_50);
                        command.Parameters.AddWithValue("@SMA_200", sma_200);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }

        public void PurgeSMA()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("TRUNCATE TABLE dbo.SMA", connection))
                    {
                        command.CommandType = CommandType.Text;
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                OptionsException oex = new OptionsException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(oex);
            }
            finally
            {

            }
        }
        #endregion
    }
}
